﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data.MySqlClient;

using seo.Data;


namespace seo.DAO
{
    public class ContentDao
    {
       private MySqlConnection conn;

       public ContentDao(MySqlConnection conn)
        {
            this.conn = conn;
        }

        public int insertContent(Content content,string prefix)
        {

            List<DbInfo> list = new List<DbInfo>();

            //MySQLCommand command = (MySQLCommand)conn.CreateCommand();
            StringBuilder insertCmd = new StringBuilder();

            insertCmd.Append("INSERT INTO  ");
            insertCmd.Append(" " + prefix + "_content  ");

            insertCmd.Append("   VALUES        ");
            insertCmd.Append(" ( ?id, ?title,        ");
            insertCmd.Append("   ?alias,        ");
            insertCmd.Append("   ?title_alias,  ");
            insertCmd.Append("   ?introtext,        ");
            insertCmd.Append("   ?fulltext,        ");
            insertCmd.Append("   ?state,        ");
            insertCmd.Append("   ?sectionid,        ");
            insertCmd.Append("   ?mask,        ");
            insertCmd.Append("   ?catid,        ");
            insertCmd.Append("   ?created,        ");
            insertCmd.Append("   ?created_by,      ");
            insertCmd.Append("   ?created_by_alias,       ");
            insertCmd.Append("   ?modified,        ");
            insertCmd.Append("   ?modified_by,        ");
            insertCmd.Append("   ?checked_out,        ");
            insertCmd.Append("   ?checked_out_time,        ");
            insertCmd.Append("   ?publish_up,        ");
            insertCmd.Append("   ?publish_down,        ");
            insertCmd.Append("   ?images,        ");
            insertCmd.Append("   ?urls,        ");
            insertCmd.Append("   ?attribs,        ");
            insertCmd.Append("   ?version,        ");
            insertCmd.Append("   ?parentid,        ");
            insertCmd.Append("   ?ordering,        ");
            insertCmd.Append("   ?metakey,        ");
            insertCmd.Append("   ?metadesc,        ");
            insertCmd.Append("   ?access,        ");
            insertCmd.Append("   ?hits,       ");
            insertCmd.Append("   ?metadata)      ");


            MySqlCommand cmd = new MySqlCommand(insertCmd.ToString(), conn);

            cmd.Parameters.AddWithValue("?id", content.id);
            cmd.Parameters.AddWithValue("?title", content.title);
            cmd.Parameters.AddWithValue("?alias", content.alias);
            cmd.Parameters.AddWithValue("?title_alias", content.title_alias);
            cmd.Parameters.AddWithValue("?introtext", content.introtext);
            cmd.Parameters.AddWithValue("?fulltext", content.fulltext);
            cmd.Parameters.AddWithValue("?state", content.state);
            cmd.Parameters.AddWithValue("?sectionid", content.sectionid);
            cmd.Parameters.AddWithValue("?mask", content.mask);
            cmd.Parameters.AddWithValue("?catid", content.catid);
            cmd.Parameters.AddWithValue("?created", content.created);
            cmd.Parameters.AddWithValue("?created_by", content.created_by);
            cmd.Parameters.AddWithValue("?created_by_alias", content.created_by_alias);
            cmd.Parameters.AddWithValue("?modified","0000-00-00 00:00:00");
            cmd.Parameters.AddWithValue("?modified_by", content.modified_by);
            cmd.Parameters.AddWithValue("?checked_out", content.checked_out);
            cmd.Parameters.AddWithValue("?checked_out_time", "0000-00-00 00:00:00");
            cmd.Parameters.AddWithValue("?publish_up", content.publish_up);
            cmd.Parameters.AddWithValue("?publish_down", "0000-00-00 00:00:00");
            cmd.Parameters.AddWithValue("?images", content.images);
            cmd.Parameters.AddWithValue("?urls", content.urls);
            cmd.Parameters.AddWithValue("?attribs", content.attribs);
            cmd.Parameters.AddWithValue("?version", content.version);
            cmd.Parameters.AddWithValue("?parentid", content.parentid);
            cmd.Parameters.AddWithValue("?ordering", content.ordering);
            cmd.Parameters.AddWithValue("?metakey", content.metakey);
            cmd.Parameters.AddWithValue("?metadesc", content.metadesc);
            cmd.Parameters.AddWithValue("?access", content.access);
            cmd.Parameters.AddWithValue("?hits", content.hits);
            cmd.Parameters.AddWithValue("?metadata", content.metadata);

            cmd.ExecuteNonQuery();

            return 0;
        }

        public int getMaxId(string prefix)
        {
            int maxId = 0;

            MySqlCommand command = conn.CreateCommand();
            MySqlDataReader reader;

            command.CommandText = "select max(id)+1 as maxId from " + prefix + "_content";

            reader = command.ExecuteReader();

            while (reader.Read())
            {
                maxId = reader.GetInt32(0) ;
            }

            reader.Close();

            return maxId;

        }

        public string[] getSectIdAndCatId(string keyword, string prefix) 
        {
            string[] idStr = new string[2];

            MySqlCommand command = conn.CreateCommand();
            MySqlDataReader reader;

           // command.CommandText = "select section,id from robbie." + prefix + "_categories ";
            command.CommandText = "select section,id from " + prefix + "_categories where title = '" + keyword + "'";

            reader = command.ExecuteReader();

            while (reader.Read())
            {
                idStr[0] = reader.GetString(0);
                idStr[1] = reader.GetString(1);
            }

            reader.Close();

            return idStr;
        
        }

        public string GetNativeUrlByKeyword(string keyword, string prefix)
        {
            string url = String.Empty;
            List<string> list = new List<string>();

            MySqlCommand command = conn.CreateCommand();
            MySqlDataReader reader;

            // command.CommandText = "select section,id from robbie." + prefix + "_categories ";
            command.CommandText = @"select concat('/index.php?option=com_content&view=article&id=',content.id,':',content.alias,'&catid=',content.catid,':',cat.alias)
                                    from " + prefix + @"_content content," + prefix + @"_categories cat
                                    where content.catid = cat.id
                                    and content.introtext like '%" + keyword + "%'";

            reader = command.ExecuteReader();

            while (reader.Read())
            {
                url = reader.GetString(0);
                list.Add(url);
            }

            reader.Close();
            if (list.Count > 0)
            {
                Random r = new Random((int)DateTime.Now.Ticks);
                url = list[r.Next(list.Count)];
            }

            return url;

        }
    }
}
